#!/usr/bin/python
#coding=utf-8
from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op, db_utils as util
import sys
from simo.createPreRCM import *
from simo.createPreQ314 import *
from simo.createPreQ12 import *
from simo.createPreQ503 import *

dtypedict = {'ACCT': VARCHAR,
            'FACTORY': VARCHAR,'COST_CENTER': VARCHAR,'UNIT_CODE': VARCHAR,'CLASS': VARCHAR,
            'SHIFT': VARCHAR,'PRODUCE_TIME': VARCHAR,'PROD_COILNO': VARCHAR,'ENTRY_COILNO': VARCHAR,
            'IND': VARCHAR,'IND_NAME': VARCHAR,'IND_UNIT': VARCHAR,
            'ACT_VALUE': DECIMAL,'PLAN_VALUE': DECIMAL,'TARGET_VALUE': DECIMAL,
            'DIFF_VALUE': DECIMAL,'TARGET_RANGE_UL': DECIMAL,'TARGET_RANGE_LL': DECIMAL,
            'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }



def MO0101A(cost_center,unit_code,start_time,end_time,tableName):
    #先删除数据
    del_sql = """delete from BGTARAS1."""+tableName+""" WHERE COST_CENTER='""" + cost_center + """' and PRODUCE_TIME BETWEEN '""" + str(
        start_time) + """' AND '""" + str(end_time) + """'
                    AND IND IN (SELECT IND FROM BGTARAS1.T_ADS_WH_SICB_MO0101 WHERE UNIT_CODE='""" + unit_code + """' AND DELETE_TYPE='A')
        """
    conn_rds.execute(del_sql)

    # 查询配置表，获取配置条件
    sql = """select IND,IND_NAME,IND_UNIT,IND_TYPE,COL_PROC_1,RELATED_TBL_1,RELATED_TERM_1,COL_PROC_2,RELATED_TBL_2,RELATED_TERM_2,EXCEPT_SIZE,IND_SEQ,SPARE_FIELD_1,SPARE_FIELD_2,DATA_SOURCE_1,DATA_SOURCE_2 from BGTARAS1.T_ADS_WH_SICB_MO0101A WHERE COST_CENTER= '""" + cost_center + """' and  UNIT_CODE= '""" + unit_code + """'"""
    df = pd.read_sql(sql, conn_rds)
    # print(df.__len__())
    # 读取这段时间的基础信息
    sql1 = """SELECT 
                    ACCT,COST_CENTER,UNIT_CODE,CLASS,SHIFT,PRODUCE_TIME,PROD_COILNO,ENTRY_COILNO,STEELNO,
                    THICK_CODE,WIDTH_CODE,ENTRY_WIDTH_CODE,ENTRY_THICK_CODE
                FROM BGTARAS1.T_ADS_FACT_SICB_CD0201
                WHERE ACCT = '1001' AND COST_CENTER =  '""" + cost_center + """' AND UNIT_CODE= '""" + unit_code + """'
                AND PRODUCE_TIME BETWEEN '""" + str(start_time) + """' AND '""" + str(end_time) + """'
            """
    # print(sql1)
    df_base = util.query(conn=conn_rds, sql=sql1)
    # 按照卷号遍历
    for info in zip(df_base['acct'], df_base['cost_center'], df_base['unit_code'], df_base['class'], df_base['shift'],
                    df_base['produce_time'], df_base['prod_coilno'], df_base['entry_coilno'],
                    df_base["steelno"], df_base["thick_code"], df_base["width_code"], df_base["entry_width_code"],
                    df_base["entry_thick_code"]):
        # 遍历动因
        for tup in zip(df['ind'], df['ind_name'], df['ind_unit'], df['col_proc_1'], df['related_tbl_1'],
                       df['related_term_1'], df['spare_field_1'], df['col_proc_2'], df['related_tbl_2'], df['related_term_2'],
                       df['spare_field_2'], df['except_size'], df['data_source_1'], df['data_source_2']):
            try:
                related_tbl_1 = op.replaceColumn(str(tup[4]),["P_MAT_NO", "P_IN_MAT_NO",'P_UNIT'],[info[6], info[7], info[2]])
                cal_where_1 = op.replaceColumn(str(tup[5]), ["P_MAT_NO", "P_IN_MAT_NO", "P_COST_CENTER","P_CLASS",'P_PRODUCE_TIME','P_UNIT'],
                                               [info[6], info[7],cost_center, info[3], info[5], info[2]])
                sql2 = """
                        select '""" + info[0] + """' AS ACCT,
                            '""" + info[1] + """' AS COST_CENTER,
                            '""" + info[2] + """' AS UNIT_CODE,
                            '""" + info[3] + """' AS CLASS,
                            '""" + info[4] + """' AS SHIFT,
                            '""" + info[5] + """' AS PRODUCE_TIME,
                            '""" + info[6] + """' AS PROD_COILNO,
                            '""" + info[7] + """' AS ENTRY_COILNO,
                            '""" + tup[0] + """' AS IND,
                            '""" + tup[1] + """' AS IND_NAME,
                            '""" + tup[2] + """' AS IND_UNIT,
                            """ + tup[3] + """ AS ACT_VALUE,
                            'BGTARAS1' AS REC_CREATOR,
                            TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                            '' AS REC_REVISOR,
                            TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                        FROM """ + related_tbl_1 + """ WHERE """ + cal_where_1 + """
                        """ + tup[6] + """
                    """
                # print(sql2)
                df_fact = util.query(conn=conn_rds if tup[12] == "RDS" else conn_db2, sql=sql2)
                # 取目标值
                cal_where_2 = op.replaceColumn(str(tup[9]),
                                               ["P_COST_CENTER", "P_UNIT", "P_TEAM", "P_SHIFT", "P_WORK_DATE", "P_MAT_NO",
                                                "P_IN_MAT_NO", "P_ST_NO", "P_THICK_COD", "P_WIDTH_COD", "P_IN_THICK_COD"],
                                               [info[1], info[2], info[3], info[4], info[5], info[6], info[7], info[8],
                                                info[9], info[10], info[12]])

                sql_aim = """
                                    SELECT '""" + info[1] + """' AS cost_center_2,
                                    '""" + info[2] + """' AS unit_code ,
                                    '""" + info[5] + """' AS produce_time ,
                                    '""" + info[6] + """' AS prod_coilno_2 ,
                                    '""" + info[7] + """' AS entry_coilno_2 ,
                                    '""" + tup[0] + """'  AS ind_2,
                                    """ + tup[7] + """ 
                                    FROM """ + tup[8] + """ where """ + cal_where_2 + tup[10] + """
                                """
                # print(sql_aim)
                df_aim = util.query(conn=conn_rds if tup[13] == "RDS" else conn_db2, sql=sql_aim)
                df_result = op.merge_df(df_fact, df_aim,
                                        [["prod_coilno", "entry_coilno", "ind"], ["prod_coilno_2", "entry_coilno_2", "ind_2"]],
                                        how="left")
                df_result = df_result[['acct', 'cost_center', 'unit_code_x', 'class', 'shift', 'produce_time_x'
                    , 'prod_coilno', 'entry_coilno', 'ind', 'ind_name', 'ind_unit', 'act_value',
                                       'target_value'
                    , 'target_range_ul', 'target_range_ll', 'rec_creator', 'rec_revisor', 'rec_revisor_time', 'rec_create_time']]
                df_result.rename(columns={'unit_code_x': 'unit_code'}, inplace=True)
                df_result.rename(columns={'produce_time_x': 'produce_time'}, inplace=True)
                util.insert(df_result, conn_rds, tableName, "BGTARAS1", dtypedict)
            except Exception as e:
                print(df['ind']+" error:"+ e )

def MO0101B(cost_center,unit_code,start_time,end_time,tableName):
    if cost_center in ('MIBH','MIBD','MIBE'):
        del_sql = """delete from BGTARAS1.""" + tableName + """ WHERE COST_CENTER='""" + cost_center + """' and PRODUCE_TIME BETWEEN '""" + str(
            start_time) + """' AND '""" + str(end_time) + """'
                        AND IND IN (SELECT IND FROM BGTARAS1.T_ADS_WH_SICB_MO0101 WHERE UNIT_CODE='""" + unit_code + """' AND DELETE_TYPE='B'
                        AND IND NOT IN ('64400_YZ_COST','64400_X_COST','64300_COST','FL'))
            """

    else:
        del_sql = """
               delete from BGTARAS1.""" + tableName + """ WHERE COST_CENTER='""" + cost_center + """' and PRODUCE_TIME BETWEEN '""" + str(
            start_time) + """' AND '""" + str(end_time) + """'
                        AND IND IN (SELECT IND FROM BGTARAS1.T_ADS_WH_SICB_MO0101 WHERE UNIT_CODE='""" + unit_code + """' AND DELETE_TYPE='B') 
        """
    print(del_sql)
    conn_rds.execute(del_sql)

    # 查询配置表，获取配置条件
    sql = """select IND,CALC_LOGIC as sql_m,EXCEPT_SIZE AS size_type from BGTARAS1.T_ADS_WH_SICB_MO0101B WHERE COST_CENTER='""" + cost_center + """'"""
    df = pd.read_sql(sql, conn_rds)
    # 遍历动因

    for tup in zip(df['ind'], df['sql_m'], df['size_type']):
        try:
            sql_m = op.replaceColumn(str(tup[1]),
                                     ["P_DATA_TYPE", "P_UNIT", "P_COST_CENTER", "P_ACCOUNT", "P_START_TIME",
                                      "P_END_TIME",
                                      "P_SIZE_TYPE"],
                                     ["0", unit_code, cost_center, "1001", start_time, end_time, str(tup[2])])
            # print(sql_m)
            df_result = util.query(conn_rds, sql=sql_m)
            util.insert(df_result, conn_rds, tableName, "BGTARAS1", dtypedict)
        except Exception as e:
            print(df['ind'] + " error:" + e)



def MO0101(cost_center,unit_code,start_time,end_time):
    now_time = datetime.datetime.now()
    #先删除这段时间的数据
    global conn_db2, conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')

    if unit_code in ('Q602','Q502','Q402','Q102','Q202','Q302'):
        tableName = 'T_ADS_FACT_SICB_MO0101_RCM'
        preRCM(unit_code,start_time,end_time)
    elif unit_code in ("Q503"):
        tableName = 'T_ADS_FACT_SICB_MO0101_RBAF'
        PreQ503(start_time, end_time)
    elif unit_code in ("Q314","Q414","Q214","Q114"):
        tableName = 'T_ADS_FACT_SICB_MO0101_FCL'
        PreQ314(start_time,end_time)
    elif unit_code in ('Q112','Q212','Q312','Q412','Q512'):
        tableName = 'T_ADS_FACT_SICB_MO0101_DCL'
        preQ12(start_time,end_time,unit_code)
    elif unit_code in ("Q418","Q518"):
        tableName = 'T_ADS_FACT_SICB_MO0101_SACL'

    MO0101A(cost_center,unit_code,start_time,end_time,tableName)
    MO0101B(cost_center, unit_code, start_time, end_time,tableName)
    #删除临时表
    sel_sql = """SELECT TABNAME FROM syscat.tables t WHERE t.tabschema ='BGTARAS1'  AND TABNAME LIKE 'PRE%'"""
    df_tables = util.query(conn_rds,sel_sql)
    for tup in zip(df_tables["tabname"]):
        del_sql = """drop table BGTARAS1."""+tup[0]+""" """
        conn_rds.execute(del_sql)

    conn_rds.close()
    conn_db2.close()
    pro_end_time = datetime.datetime.now() - now_time
    print(unit_code+":用时"+str(pro_end_time.seconds)+"秒")


def runMO0101(type,p_start_time,p_end_time):
    if type == "RCM":
        MO0101('MIBH', 'Q602', p_start_time, p_end_time)
        MO0101('MIBD', 'Q402', p_start_time, p_end_time)
        MO0101('MIBE', 'Q502', p_start_time, p_end_time)
        MO0101('MIBA', 'Q102', p_start_time, p_end_time)
        MO0101('MIBB', 'Q202', p_start_time, p_end_time)
        MO0101('MIBC', 'Q302', p_start_time, p_end_time)
    elif type == "DCL":
        MO0101('MIJA', 'Q512', p_start_time, p_end_time)
        MO0101('MIHB', 'Q412', p_start_time, p_end_time)
        MO0101('MIHA', 'Q312', p_start_time, p_end_time)
        MO0101('MICB', 'Q212', p_start_time, p_end_time)
        MO0101('MICA', 'Q112', p_start_time, p_end_time)
    elif type == "RBAF":
        MO0101('MIJC', 'Q503', p_start_time, p_end_time)
    elif type == "FCL":
        MO0101('MIJD', 'Q414', p_start_time, p_end_time)
        MO0101('MIHF', 'Q314', p_start_time, p_end_time)
        MO0101('MIHE', 'Q214', p_start_time, p_end_time)
        MO0101('MICE', 'Q114', p_start_time, p_end_time)
    elif type == "SACL":
        MO0101('MIDE', 'Q518', p_start_time, p_end_time)
        MO0101('MIDD', 'Q418', p_start_time, p_end_time)


if __name__ == '__main__':
    '''先读配置表
      再获取RDS连接
    '''
    now_time = datetime.datetime.now()
    start_time = (datetime.datetime.now() - datetime.timedelta(minutes=360)).strftime("%Y%m%tmp_dict%H%M%S")
    end_time = datetime.datetime.now().strftime("%Y%m%tmp_dict%H%M%S")
    start_time = '20211213200000'
    end_time = '20211214200000'
    MO0101('MIBH', 'Q602', start_time, end_time)